﻿-- =========================================================================================================
-- Author:		MICHAEL RITACCO
-- Copyright:	2010 MEKHANO GROUP LLC
-- Version:		08/21/2010 1.0
-- =========================================================================================================
CREATE FUNCTION [flcr].[parmSplit](@sText varchar(max), @sDelim varchar(20) = ',')
RETURNS @retArray TABLE (id smallint Primary Key, value varchar(max))
AS
BEGIN
	DECLARE @id smallint,
		@value varchar(max),
		@bcontinue bit,
		@iStrike smallint,
		@iDelimlength tinyint

	IF @sDelim = 'Space'
		BEGIN
			SET @sDelim = ' '
		END
		
		SET @id = 0
		SET @sText = LTrim(RTrim(@sText))
		SET @iDelimlength = DATALENGTH(@sDelim)
		SET @bcontinue = 1

	IF NOT ((@iDelimlength = 0) or (@sDelim = 'Empty'))
	BEGIN
		WHILE @bcontinue = 1
		BEGIN

	-- If you can find the delimiter in the text, retrieve the first element and
	-- Insert it with its index into the return table.

		IF CHARINDEX(@sDelim, @sText)>0
			BEGIN
				SET @value = SUBSTRING(@sText,1, CHARINDEX(@sDelim,@sText)-1)
				BEGIN
					INSERT @retArray (id, value)
					VALUES (@id, @value)
				END
	-- Trim the element and its delimiter from the front of the string.
	-- Increment the index and loop. */
				SET @iStrike = DATALENGTH(@value) + @iDelimlength
				SET @id = @id + 1
				SET @sText = LTrim(Right(@sText,DATALENGTH(@sText) - @iStrike))
			END
		ELSE
			BEGIN
			--If you can’t find the delimiter in the text, @sText is the last value in
			--@retArray.
				SET @value = @sText
					BEGIN
						INSERT @retArray (id, value)
						VALUES (@id, @value)
					END
					--Exit the WHILE loop.
				SET @bcontinue = 0
				END
			END
		END
	ELSE
		BEGIN
		WHILE @bcontinue=1
			BEGIN
			--If the delimiter is an empty string, check for remaining text
			--instead of a delimiter. Insert the first character into the
			--retArray table. Trim the character from the front of the string.
			--Increment the index and loop.
			IF DATALENGTH(@sText)>1
				BEGIN
					SET @value = SUBSTRING(@sText,1,1)
					BEGIN
						INSERT @retArray (id, value)
						VALUES (@id, @value)
					END
					SET @id = @id+1
					SET @sText = SUBSTRING(@sText,2,DATALENGTH(@sText)-1)
				END
			ELSE
				BEGIN
				--One character remains.
				--Insert the character, and exit the WHILE loop.
					INSERT @retArray (id, value)
					VALUES (@id, @sText)
					SET @bcontinue = 0	
				END
	END
END
RETURN
END